package com.lanvideo.dao;

import com.lanvideo.entity.Exam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 考试DAO接口
 */
@Repository
public class ExamDao {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 创建考试
     */
    public int createExam(Exam exam) {
        String sql = "INSERT INTO exam (title, description, teacher_id, time_limit, status) VALUES (?, ?, ?, ?, ?)";
        return jdbcTemplate.update(sql, exam.getTitle(), exam.getDescription(), exam.getTeacherId(), 
                exam.getTimeLimit(), exam.getStatus());
    }

    /**
     * 更新考试
     */
    public int updateExam(Exam exam) {
        String sql = "UPDATE exam SET title=?, description=?, total_questions=?, total_score=?, time_limit=?, status=? WHERE id=?";
        return jdbcTemplate.update(sql, exam.getTitle(), exam.getDescription(), exam.getTotalQuestions(), 
                exam.getTotalScore(), exam.getTimeLimit(), exam.getStatus(), exam.getId());
    }

    /**
     * 删除考试
     */
    public int deleteExam(Integer examId) {
        String sql = "DELETE FROM exam WHERE id=?";
        return jdbcTemplate.update(sql, examId);
    }

    /**
     * 根据ID获取考试
     */
    public Exam getExamById(Integer examId) {
        String sql = "SELECT e.*, u.name as teacher_name FROM exam e LEFT JOIN user u ON e.teacher_id = u.id WHERE e.id=?";
        List<Exam> exams = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Exam.class), examId);
        return exams.isEmpty() ? null : exams.get(0);
    }

    /**
     * 获取老师创建的所有考试
     */
    public List<Exam> getExamsByTeacherId(Integer teacherId) {
        String sql = "SELECT e.*, u.name as teacher_name, " +
                "(SELECT COUNT(*) FROM exam_student es WHERE es.exam_id = e.id) as student_count, " +
                "(SELECT COUNT(*) FROM exam_student es WHERE es.exam_id = e.id AND es.status = 2) as completed_count " +
                "FROM exam e LEFT JOIN user u ON e.teacher_id = u.id WHERE e.teacher_id=? ORDER BY e.created_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Exam.class), teacherId);
    }

    /**
     * 获取学生可参与的考试
     */
    public List<Exam> getAvailableExamsForStudent(Integer studentId) {
        String sql = "SELECT e.*, u.name as teacher_name, " +
                "es.status as student_status, es.score as student_score, " +
                "(SELECT COUNT(*) FROM exam_student WHERE exam_id = e.id) as student_count " +
                "FROM exam e " +
                "LEFT JOIN user u ON e.teacher_id = u.id " +
                "LEFT JOIN exam_student es ON e.id = es.exam_id AND es.student_id = ? " +
                "WHERE e.status = 1 " +
                "ORDER BY e.created_time DESC";
        
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            Exam exam = new Exam();
            exam.setId(rs.getInt("id"));
            exam.setTitle(rs.getString("title"));
            exam.setDescription(rs.getString("description"));
            exam.setTeacherId(rs.getInt("teacher_id"));
            exam.setTotalQuestions(rs.getInt("total_questions"));
            exam.setExamQuestions(rs.getInt("total_questions")); // 使用total_questions作为exam_questions
            exam.setTotalScore(rs.getInt("total_score"));
            exam.setTimeLimit(rs.getInt("time_limit"));
            exam.setStatus(rs.getInt("status"));
            exam.setCreatedTime(rs.getTimestamp("created_time").toLocalDateTime());
            exam.setUpdatedTime(rs.getTimestamp("updated_time").toLocalDateTime());
            
            // 关联字段
            exam.setTeacherName(rs.getString("teacher_name"));
            exam.setStudentCount(rs.getInt("student_count"));
            
            // 学生状态字段 - 如果学生没有参与考试，student_status为null
            Integer studentStatus = rs.getObject("student_status", Integer.class);
            exam.setStudentStatus(studentStatus);
            exam.setStudentScore(rs.getObject("student_score", Integer.class));
            
            return exam;
        }, studentId);
    }

    /**
     * 获取所有考试（管理员）
     */
    public List<Exam> getAllExams() {
        String sql = "SELECT e.*, u.name as teacher_name, " +
                "(SELECT COUNT(*) FROM exam_student es WHERE es.exam_id = e.id) as student_count, " +
                "(SELECT COUNT(*) FROM exam_student es WHERE es.exam_id = e.id AND es.status = 2) as completed_count " +
                "FROM exam e LEFT JOIN user u ON e.teacher_id = u.id ORDER BY e.created_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Exam.class));
    }

    /**
     * 更新考试状态
     */
    public int updateExamStatus(Integer examId, Integer status) {
        String sql = "UPDATE exam SET status=? WHERE id=?";
        return jdbcTemplate.update(sql, status, examId);
    }

    /**
     * 获取已发布的考试列表
     */
    public List<Exam> getPublishedExams() {
        String sql = "SELECT e.*, u.name as teacher_name FROM exam e " +
                "LEFT JOIN user u ON e.teacher_id = u.id " +
                "WHERE e.status = 1 " +
                "ORDER BY e.created_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Exam.class));
    }

    /**
     * 获取老师的考试列表
     */
    public List<Exam> getTeacherExams(Integer teacherId) {
        String sql = "SELECT e.*, u.name as teacher_name, " +
                "(SELECT COUNT(*) FROM exam_student es WHERE es.exam_id = e.id) as student_count, " +
                "(SELECT COUNT(*) FROM exam_student es WHERE es.exam_id = e.id AND es.status = 1) as completed_count " +
                "FROM exam e " +
                "LEFT JOIN user u ON e.teacher_id = u.id " +
                "WHERE e.teacher_id = ? " +
                "ORDER BY e.created_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Exam.class), teacherId);
    }

}
